﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AsposeCellTest
{
    class Program
    {
        static void Main(string[] args)
        {
            ValiInt();
            ValiDecimal();
            ValiList();
            ValiDate();
            ValiTextLength();
            ValiTime();

            Test1();
            Test2();
        }

        public static void ValiInt()
        {
            // Create a workbook object.
            Workbook workbook = new Workbook();

            // Get the first worksheet.
            Worksheet worksheet1 = workbook.Worksheets[0];

            ValidationCollection validations = worksheet1.Validations;

            //Applying the validation to a range of cells from A1 to B2 using the
            //CellArea structure
            CellArea area = new CellArea();

            area.StartRow = 0;
            area.EndRow = 1000001;
            area.StartColumn = 0;
            area.EndColumn = 0;

            //Creating a Validation object
            Validation validation = validations[validations.Add(area)];

            //Setting the validation type to whole number
            validation.Type = ValidationType.WholeNumber;

            //Setting the operator for validation to Between
            validation.Operator = OperatorType.Between;

            //Setting the minimum value for the validation
            validation.Formula1 = "10";

            //Setting the maximum value for the validation
            validation.Formula2 = "1000";

            string filePath = Path.GetFullPath("validationInt.xlsx");

            // Save the Excel file.
            workbook.Save(filePath);
        }

        public static void ValiDecimal()
        {
            // Create a workbook object.
            Workbook workbook = new Workbook();

            // Create a worksheet and get the first worksheet.
            Worksheet worksheet1 = workbook.Worksheets[0];

            // Obtain the existing Validations collection.
            ValidationCollection validations = worksheet1.Validations;

            // Specify the validation area of cells.
            CellArea area = new CellArea();
            area.StartRow = 0;
            area.EndRow = 1000001;
            area.StartColumn = 0;
            area.EndColumn = 0;

            // Create a validation object adding to the collection list.
            Validation validation = validations[validations.Add(area)];

            // Set the validation type.
            validation.Type = ValidationType.Decimal;

            // Specify the operator.
            validation.Operator = OperatorType.Between;

            // Set the lower and upper limits.
            validation.Formula1 = Decimal.MinValue.ToString();
            validation.Formula2 = Decimal.MaxValue.ToString();

            // Set the error message.
            validation.ErrorMessage = "Please enter a valid integer or decimal number";


            // Set the number formats to 2 decimal places for the validation area.
            for (int i = 0; i < 10; i++)
            {
                worksheet1.Cells[i, 0].GetStyle().Custom = "0.00";
            }

            string filePath = Path.GetFullPath("validationDecimal.xlsx");
            // Save the workbook.
            workbook.Save(filePath);
        }

        public static void ValiList()
        {
            // Create a workbook object.
            Workbook workbook = new Workbook();

            // Get the first worksheet.
            Worksheet worksheet1 = workbook.Worksheets[0];

            // Get the validations collection.
            ValidationCollection validations = worksheet1.Validations;


            // Specify the validation area.
            CellArea area = new CellArea();
            area.StartRow = 0;
            area.EndRow = 1000001;
            area.StartColumn = 0;
            area.EndColumn = 0;

            // Create a new validation to the validations list.
            Validation validation = validations[validations.Add(area)];

            // Set the validation type.
            validation.Type = Aspose.Cells.ValidationType.List;

            // Set the operator.
            validation.Operator = OperatorType.None;

            // Set the in cell drop down.
            validation.InCellDropDown = true;

            // Set the formula1.
            validation.Formula1 = "111,222,333,444";

            // Enable it to show error.
            validation.ShowError = true;

            // Set the alert type severity level.
            validation.AlertStyle = ValidationAlertType.Stop;

            // Set the error title.
            validation.ErrorTitle = "Error";

            // Set the error message.
            validation.ErrorMessage = "Please select a color from the list";


            string filePath = Path.GetFullPath("validationtypeList.xlsx");

            // Save the Excel file.
            workbook.Save(filePath);
        }

        public static void ValiDate()
        {
            // Create a workbook.
            Workbook workbook = new Workbook();

            // Get the validations collection.
            ValidationCollection validations = workbook.Worksheets[0].Validations;

            // Set a collection of CellArea which contains the data validation settings.
            CellArea area = new CellArea();
            area.StartRow = 0;
            area.EndRow = 1000001;
            area.StartColumn = 0;
            area.EndColumn = 0;

            // Add a new validation.
            Validation validation = validations[validations.Add(area)];

            // Set the data validation type.
            validation.Type = ValidationType.Date;

            // Set the operator for the data validation
            validation.Operator = OperatorType.Between;

            // Set the value or expression associated with the data validation.
            validation.Formula1 = "1/1/1970";

            // The value or expression associated with the second part of the data validation.
            validation.Formula2 = "12/31/1999";

            // Enable the error.
            validation.ShowError = true;

            // Set the validation alert style.
            validation.AlertStyle = ValidationAlertType.Stop;

            // Set the title of the data-validation error dialog box
            validation.ErrorTitle = "Date Error";

            // Set the data validation error message.
            validation.ErrorMessage = "Enter a Valid Date";

            // Set and enable the data validation input message.
            validation.InputMessage = "Date Validation Type";
            validation.IgnoreBlank = true;
            validation.ShowInput = true;


            string filePath = Path.GetFullPath("validationDate.xlsx");
            // Save the Excel file.
            workbook.Save(filePath);
        }

        public static void ValiTextLength()
        {
            // Create a new workbook.
            Workbook workbook = new Workbook();

            // Obtain the cells of the first worksheet.
            Cells cells = workbook.Worksheets[0].Cells;

            //Put a string value into A1 cell.
            cells["A1"].PutValue("Please enter a string not more than 5 chars");

            // Wrap the text.
            cells["A1"].GetStyle().IsTextWrapped = true;

            // Set row height and column width for the cell.
            cells.SetRowHeight(0, 31);
            cells.SetColumnWidth(0, 35);

            // Get the validations collection.
            ValidationCollection validations = workbook.Worksheets[0].Validations;

            // Set a collection of CellArea which contains the data validation settings.
            CellArea area = new CellArea();
            area.StartRow = 0;
            area.EndRow = 0;
            area.StartColumn = 1;
            area.EndColumn = 1;


            // Add a new validation.
            Validation validation = validations[validations.Add(area)];

            // Set the data validation type.
            validation.Type = ValidationType.TextLength;

            // Set the operator for the data validation.
            validation.Operator = OperatorType.LessOrEqual;

            // Set the value or expression associated with the data validation.
            validation.Formula1 = "5";

            // Enable the error.
            validation.ShowError = true;

            // Set the validation alert style.
            validation.AlertStyle = ValidationAlertType.Warning;

            // Set the title of the data-validation error dialog box.
            validation.ErrorTitle = "Text Length Error";

            // Set the data validation error message.
            validation.ErrorMessage = " Enter a Valid String";

            // Set and enable the data validation input message.
            validation.InputMessage = "TextLength Validation Type";
            validation.IgnoreBlank = true;
            validation.ShowInput = true;


            string filePath = Path.GetFullPath("validationTextLength.xlsx");

            // Save the Excel file.
            workbook.Save(filePath);
        }


        public static void ValiTime()
        {
            // Create a workbook.
            Workbook workbook = new Workbook();

            // Get the validations collection.
            ValidationCollection validations = workbook.Worksheets[0].Validations;

            // Set a collection of CellArea which contains the data validation settings.
            CellArea area = new CellArea();
            area.StartRow = 0;
            area.EndRow = 1000001;
            area.StartColumn = 0;
            area.EndColumn = 0;

            // Add a new validation.
            Validation validation = validations[validations.Add(area)];

            // Set the data validation type.
            validation.Type = ValidationType.Time;

            // Set the operator for the data validation
            validation.Operator = OperatorType.Between;

            // Set the value or expression associated with the data validation.
            validation.Formula1 = "1/1/1970 00:00:00.000";

            // The value or expression associated with the second part of the data validation.
            validation.Formula2 = "12/31/9999 00:00:00.000";

            // Enable the error.
            validation.ShowError = true;

            // Set the validation alert style.
            validation.AlertStyle = ValidationAlertType.Stop;

            // Set the title of the data-validation error dialog box
            validation.ErrorTitle = "Time Error";

            // Set the data validation error message.
            validation.ErrorMessage = "Enter a Valid Time";

            // Set and enable the data validation input message.
            validation.InputMessage = "Time Validation Type";
            validation.IgnoreBlank = true;
            validation.ShowInput = true;


            string filePath = Path.GetFullPath("validationTime.xlsx");
            // Save the Excel file.
            workbook.Save(filePath);
        }


        public static void Test1()
        {
            
        }

        public static void Test2()
        {
            
        }
    }
}
